﻿using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using log4net;

namespace ADS2Excel
{
    class ExcelHelper
    {
        private static string xlsPath_L = ConfigurationManager.AppSettings["excelLPath"];
        private static string xlsPath_R = ConfigurationManager.AppSettings["excelRPath"];
        private static string xlsPatMJWZ = ConfigurationManager.AppSettings["excelMJWZPath"];
        const string PassWord = "147258";


        public static void CreateExcelFile(SymbolConfig.Flag_Direction f, List<DataADS> l)
        {
            DataTable dataTable = new DataTable();

            dataTable.Columns.Add("时间");
            for (int i = 1; i <= 250; i++)
            {
                dataTable.Columns.Add("支架" + i.ToString());
            }

            if (f == SymbolConfig.Flag_Direction.L)
            {
                if (!File.Exists(xlsPath_L))
                {
                    FileInfo newFile = new FileInfo(xlsPath_L);

                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {

                        foreach (var lsy in l)
                        {
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(lsy.name);
                            //Add the headers
                            for (int i = 1; i <= 251; i++)
                            {
                                worksheet.Cells[1, i].Value = dataTable.Columns[i - 1].ColumnName;
                            }
                        }
                        package.Save();

                    }
                }
            }
            else
            {
                if (!File.Exists(xlsPath_R))
                {
                    FileInfo newFile = new FileInfo(xlsPath_R);

                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {
                        foreach (var lsy in l)
                        {
                            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(lsy.name);
                            //Add the headers
                            for (int i = 1; i <= 251; i++)
                            {
                                worksheet.Cells[1, i].Value = dataTable.Columns[i - 1].ColumnName;
                            }
                        }
                        package.Save();

                    }
                }
            }

        }

        public static void CreateExcelFile(String FileName)
        {
            DataTable dataTable = new DataTable();

            dataTable.Columns.Add("时间");
            dataTable.Columns.Add(FileName);

            if (!File.Exists(xlsPatMJWZ))
            {
                FileInfo newFile = new FileInfo(xlsPatMJWZ);

                using (ExcelPackage package = new ExcelPackage(newFile))
                {

                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("煤机位置");
                    //Add the headers
                    worksheet.Cells[1, 1].Value = dataTable.Columns[0].ColumnName;
                    worksheet.Cells[1, 2].Value = dataTable.Columns[1].ColumnName;
                    package.Save();

                }
            }

        }
        public static DataTable GenDataTable()
        {
            DataTable dataTable = new DataTable();

            return dataTable;
        }
        public static void SaveToXlsXL(string nowtime, List<DataADS> ldataADS)
        {
            try
            {
                // ok, we can run the real code of the sample now
                if (File.Exists(xlsPath_L)) //如果存在则直接打开
                {
                    FileInfo newFile = new FileInfo(xlsPath_L);

                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {
                        // add a new worksheet to the empty workbook
                        foreach (var l in ldataADS)
                        {
                            ExcelWorksheet worksheet = package.Workbook.Worksheets[l.name];
                            //获得最后一行，这里其实获取的是总行数，追加数据的话，需要+1
                            int lastRow = worksheet.Dimension.End.Row;
                            worksheet.Cells[lastRow + 1, 1].Value = nowtime;
                            for (int i = 2; i < 252; i++)
                            {
                                worksheet.Cells[lastRow + 1, i].Value = l.data[i - 2];
                            }
                        }
                        package.Save();
                    }
                }

            }
            catch (Exception e)
            {

            }
        }

        public static void SaveToXlsXR(string nowtime, List<DataADS> ldataADS)
        {
            try
            {
                
                if (File.Exists(xlsPath_R)) //如果存在则直接打开
                {
                    FileInfo newFile = new FileInfo(xlsPath_R);

                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {
                        // add a new worksheet to the empty workbook
                        foreach (var l in ldataADS)
                        {
                            ExcelWorksheet worksheet = package.Workbook.Worksheets[l.name];
                            //获得最后一行，这里其实获取的是总行数，追加数据的话，需要+1
                            int lastRow = worksheet.Dimension.End.Row;
                            worksheet.Cells[lastRow + 1, 1].Value = nowtime;
                            for (int i = 2; i < 252; i++)
                            {
                                worksheet.Cells[lastRow + 1, i].Value = l.data[i - 2];
                            }
                        }
                        package.Save();
                    }
                }
            }
            catch (Exception e)
            {

            }
        }

        public static void SaveToXlsXLocation(string nowtime, ushort location)
        {
            try
            {
                if (File.Exists(xlsPatMJWZ)) //如果存在则直接打开
                {
                    FileInfo newFile = new FileInfo(xlsPatMJWZ);

                    using (ExcelPackage package = new ExcelPackage(newFile))
                    {
                        // add a new worksheet to the empty workbook                   
                        ExcelWorksheet worksheet = package.Workbook.Worksheets["煤机位置"];
                        //获得最后一行，这里其实获取的是总行数，追加数据的话，需要+1
                        int lastRow = worksheet.Dimension.End.Row;
                        worksheet.Cells[lastRow + 1, 1].Value = nowtime;
                        worksheet.Cells[lastRow + 1, 2].Value = location;
                        package.Save();
                    }
                }else
                {
                    CreateExcelFile("煤机位置");
                }
            }
            catch (Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.ToString());
            }
        }
    }
}
